package no.ntnu.fp.storage.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import no.ntnu.fp.handlers.exceptions.DatabaseException;
import no.ntnu.fp.model.Employee;
import no.ntnu.fp.model.Meeting;
import no.ntnu.fp.model.MeetingRoom;
import no.ntnu.fp.server.ConnectionProvider;
import no.ntnu.fp.storage.MeetingStorage;
import no.ntnu.fp.utils.Participant;

public class MeetingStorageImpl implements MeetingStorage {

	private final MeetingRoomStorageImpl meetingRoomStorageImpl;
	private final EmployeeStorageImpl employeeStorageImpl;

	public MeetingStorageImpl(MeetingRoomStorageImpl mrsi,
			EmployeeStorageImpl employeeStorageImpl) {
		this.meetingRoomStorageImpl = mrsi;
		this.employeeStorageImpl = employeeStorageImpl;
	}

	public List<Meeting> getMeetingsByEmployee(Employee employee) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"SELECT * FROM (Meeting LEFT OUTER JOIN ParticipatesIn " +
					"ON Meeting.appointmentId = ParticipatesIn.appointmentId) " + 
					"WHERE (Meeting.employeeId = ? " + 
					"OR ParticipatesIn.employeeId = ? " +
					"AND ParticipatesIn.isComing = 1) " + 
			"AND Meeting.isCancelled <> 1");	

			stmt.setLong(1, employee.getId());
			stmt.setLong(2, employee.getId());

			ResultSet resultSet = stmt.executeQuery();

			Set<Meeting> meetings = new HashSet<Meeting>();
			while (resultSet.next()) {
				meetings.add(getCurrentMeeting(resultSet, connection, employee));
			}			

			stmt.close();

			return new ArrayList<Meeting>(meetings);

		} catch (SQLException ex) {
			throw new DatabaseException("Could not get meetings for " + employee, ex);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	private Meeting getCurrentMeeting(ResultSet resultSet, Connection connection,
			Employee employee) throws SQLException {
		long id = resultSet.getLong("appointmentId");
		String title = resultSet.getString("title");
		String description = resultSet.getString("description");
		String place = resultSet.getString("place");
		MeetingRoom meetingRoom = null;

		if (place == null) {
			long meetingRoomId = resultSet.getLong("meetingRoomId"); 
			if (meetingRoomId != 0) {
				meetingRoom = meetingRoomStorageImpl.getMeetingRoom(meetingRoomId, connection);
			}
		}
		long employeeId = resultSet.getLong("employeeId");
		if (employee == null || employeeId != employee.getId()) {
			employee = employeeStorageImpl.getEmployee(employeeId, connection);
		}

		Time startTime = resultSet.getTime("startTime");
		Time endTime = resultSet.getTime("endTime");
		Date appointmentDate = resultSet.getDate("appointmentDate");

		boolean isCancelled = resultSet.getBoolean("isCancelled");

		List<Participant> participants = getParticipants(id, connection);

		return new Meeting(id, place, title, description, meetingRoom, employee,  
				startTime, endTime, appointmentDate, isCancelled, participants);
	}

	private List<Participant> getParticipants(long meetingId, Connection connection)
	throws SQLException {
		//		PreparedStatement stmt = connection.prepareStatement(
		//				"SELECT * " +
		//				"FROM (" +
		//						"Meeting JOIN ParticipatesIn " +
		//						"ON Meeting.appointmentId = ParticipatesIn.appointmentId" +
		//					  ") " +
		//					"JOIN Employee " + 
		//					"ON Employee.employeeId = ParticipatesIn.employeeId " +
		//				"WHERE Meeting.appointmentId = ? " +
		//				"AND (ParticipatesIn.isComing IS NULL " +
		//				"		OR ParticipatesIn.isComing = 1) " + 
		//				"AND Meeting.isCancelled <> 1");

		PreparedStatement stmt = connection.prepareStatement(
				"SELECT * FROM " +
				"ParticipatesIn JOIN Employee " +
				"ON ParticipatesIn.employeeId = Employee.employeeId " +
		"WHERE ParticipatesIn.appointmentId = ? ");

		stmt.setLong(1, meetingId);

		ResultSet resultSet = stmt.executeQuery();

		List<Participant> participants = new ArrayList<Participant>();
		while (resultSet.next()) {
			Employee emp = employeeStorageImpl.getCurrentEmployee(resultSet);
			String coming = resultSet.getString("isComing");
			Boolean attending = (coming == null ? null : resultSet.getBoolean("isComing"));
			participants.add(new Participant(emp, attending));
		}
		stmt.close();

		return participants;
	}

	Meeting getMeetingById(long meetingId, Connection connection) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"SELECT * FROM Meeting " +
		"WHERE appointmentId = ?");
		stmt.setLong(1, meetingId);

		ResultSet resultSet = stmt.executeQuery();

		if (resultSet.next()) {
			Meeting meeting = getCurrentMeeting(resultSet, connection, null);
			stmt.close();
			return meeting;
		} else {
			stmt.close();
			throw new SQLException("No meeting with id = #" + meetingId);
		}
	}

	@Override
	public Meeting addMeeting(Meeting meeting) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"INSERT INTO Meeting " +
					"VALUES(DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);

			stmt.setLong(1, meeting.getEmployee().getId());
			stmt.setDate(2, meeting.getDate());
			stmt.setTime(3, meeting.getStartTime());
			stmt.setTime(4, meeting.getEndTime());
			stmt.setString(5, meeting.getDescription());
			stmt.setString(6, meeting.getPlace());

			MeetingRoom room = meeting.getRoom();
			if (room != null) {
				stmt.setLong(7, room.getId());
			} else {
				stmt.setNull(7, Types.INTEGER);
			}

			stmt.setString(8, meeting.getTitle());
			stmt.setBoolean(9, meeting.isCancelled());

			stmt.executeUpdate();

			ResultSet keys = stmt.getGeneratedKeys();

			if (keys.next()) {
				long meetingId = keys.getLong(1);
				stmt.close();
				updateParticipants(meetingId, meeting.getParticipants(), connection);
				meeting = getMeetingById(meetingId, connection);
			} else {
				stmt.close();
				throw new SQLException(
						"No keys generated upon inserting " + meeting);
			}

			
			return meeting;
		} catch (SQLException e) {
			throw new DatabaseException("Could not add meeting " + meeting, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	/**
	 * 
	 * @param meeting
	 * @param connection
	 * @return The employees which were removed from the meeting
	 * @throws SQLException 
	 */
	private List<Participant> updateParticipants(long meetingId, 
			List<Participant> participants, Connection connection) 
														throws SQLException {
		List<Participant> oldList = getParticipants(meetingId, connection);
		PreparedStatement stmt = connection.prepareStatement(
				"DELETE FROM ParticipatesIn " +
		"WHERE appointmentId = ?");
		stmt.setLong(1, meetingId);
		stmt.executeUpdate();

		for(Participant p : participants){
			stmt = connection.prepareStatement("INSERT INTO ParticipatesIn" +
					"(employeeId, appointmentId, isComing) " +
			"VALUES(?,?,?)");
			stmt.setLong(1, p.getEmployee().getId());
			stmt.setLong(2, meetingId);
			if(p.isAttending() == null){
				stmt.setNull(3, Types.SMALLINT);
			}else{
				stmt.setBoolean(3, p.isAttending());
			}
			stmt.executeUpdate();
		}
		//M� ogs� f� ut de slettede p� et vis - dette er for updateMeetings del, trenger ikke egentlig dette til new meeting ;D:
		oldList.removeAll(participants);
		return oldList;
	}

	@Override
	public List<Participant> updateMeeting(Meeting meeting) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			
			//This method has been all but copy-pasted from 
			//AppointmentStorageImpl.update, and has not been double-checked
			//for correctness. It may thus have unfortunate bugs.
			//See AppointmentStorageImpl.update for explanatory comments 
			
			MeetingRoom meetingRoom = meeting.getRoom();
			String place = meeting.getPlace();
			
			PreparedStatement stmt = connection
					.prepareStatement("UPDATE Meeting "
							+ "SET place = NULL " + "WHERE appointmentId = ?");
			stmt.setLong(1, meeting.getId());
			stmt.executeUpdate();
			stmt.close();
			stmt = connection.prepareStatement("UPDATE Meeting "
					+ "SET meetingRoomId = NULL " + "WHERE appointmentId = ?");
			stmt.setLong(1, meeting.getId());
			stmt.executeUpdate();
			stmt.close();
			
			updateTimeAndRoomInfo(meeting, connection, meetingRoom);
			updateContentAndPlaceInfo(meeting, connection, place);
			
			List<Participant> oldParticipants = updateParticipants(
					meeting.getId(), meeting.getParticipants(), connection);

			return oldParticipants;
		} catch (SQLException e) {
			throw new DatabaseException("Could not update appointment " 
					+ meeting, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}
	
	private void updateContentAndPlaceInfo(Meeting meeting,
		Connection connection, String place) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"UPDATE Meeting " +
				"SET description = ?, " +
					"title = ?, " +
					"place = ?," +
					"isCancelled = ? " +
					"WHERE appointmentId = ?");
		stmt.setString(1, meeting.getDescription());
		stmt.setString(2, meeting.getTitle());
		if (place == null) {
			stmt.setNull(3, Types.VARCHAR);
		} else {
			stmt.setString(3, place);
		}
		stmt.setBoolean(4, meeting.isCancelled());
		stmt.setLong(5, meeting.getId());
		stmt.executeUpdate();
		stmt.close();
		
	}

	private void updateTimeAndRoomInfo(Meeting meeting,
		Connection connection, MeetingRoom meetingRoom) throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"UPDATE Meeting " +
				"SET meetingRoomId = ? " +
				"WHERE appointmentId = ?");
		if (meetingRoom == null) {
			stmt.setNull(1, Types.INTEGER);
		} else {
			stmt.setLong(1, meetingRoom.getId());
		}
		stmt.setLong(2, meeting.getId());
		stmt.executeUpdate();
		stmt.close();
		
		stmt = connection.prepareStatement(
				"UPDATE Meeting " +
				"SET appointmentDate = ?, " +
					"startTime = ?, " +
					"endTime = ? " +
					"WHERE appointmentId = ?");
		
		stmt.setDate(1, meeting.getDate());
		stmt.setTime(2, meeting.getStartTime());
		stmt.setTime(3, meeting.getEndTime());
		stmt.setLong(4, meeting.getId());
		stmt.executeUpdate();
		stmt.close();
	}
	
	@Override
	public void cancelMeeting(Meeting meeting) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement(
					"UPDATE Meeting " +
					"SET isCancelled = 1 " +
			"WHERE appointmentId = ?");
			stmt.setLong(1, meeting.getId());

			stmt.executeUpdate();

			stmt.close();

		} catch (SQLException e) {
			throw new DatabaseException("Could not delete appointment " +  meeting, e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}

	@Override
	public Meeting markAsParticipating(Meeting meeting, Employee participant) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			setParticipatingStatus(meeting, participant, true, connection);
			return getMeetingById(meeting.getId(), connection);
		} catch (SQLException e) {
			throw new DatabaseException(String.format(
					"Unable to mark participant %s as participating in meeting %s",
					participant, meeting), e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}	

	@Override
	public Meeting  markNotParticipating(Meeting meeting, Employee participant) {
		Connection connection = ConnectionProvider.requestConnection();
		try {
			setParticipatingStatus(meeting, participant, false, connection);
			return getMeetingById(meeting.getId(), connection);
		} catch (SQLException e) {
			throw new DatabaseException(String.format(
					"Unable to mark participant %s as not participating in meeting %s",
					participant, meeting), e);
		} finally {
			ConnectionProvider.relinquishConnection();
		}
	}	

	private void setParticipatingStatus(
			Meeting meeting, Employee employee, 
			boolean participating, Connection connection) 
	throws SQLException {
		PreparedStatement stmt = connection.prepareStatement(
				"UPDATE ParticipatesIn " +
				"SET isComing = ? " +
				"WHERE employeeId = ? " +
		"AND appointmentId = ?");

		stmt.setBoolean(1, participating);
		stmt.setLong(2, employee.getId());
		stmt.setLong(3, meeting.getId());

		stmt.executeUpdate();

		stmt.close();
	}

}
